﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using System.IO;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data.Odbc;
using System.Data.OleDb;

namespace LogExport
{
    public partial class MainForm : Form
    {
        #region 成员
        string IIS_DIR = string.Empty;
        DBType DBType = DBType.None;
        string DbUser = string.Empty;
        string DbPass = string.Empty;
        string DbAddr = string.Empty;
        string DbName = string.Empty;
        string DbTNam = string.Empty;
        #endregion

        #region 载入WINFORM
        public MainForm()
        {
            InitializeComponent();
#if DEBUG
            IIS_DIR = @"C:\WINDOWS\system32\LogFiles\W3SVC1872720702";
#endif
        }
        #endregion

        #region 选择目录
        private void button1_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            dialog.Description = "请选择一个要导出的IIS目志目录\r\n\r\n如果不清楚可以到网站属性->网站->属性中可以找到";
            dialog.SelectedPath = @"C:\WINDOWS\system32\LogFiles";
            dialog.ShowNewFolderButton = false;
            DialogResult result = dialog.ShowDialog(this);
            if (result == DialogResult.OK)
            {
                IIS_DIR = dialog.SelectedPath;
            }
        } 
        #endregion

        #region 配置连接
        private void button2_Click(object sender, EventArgs e)
        {
            DbSettings dialog = new DbSettings();
            DialogResult result = dialog.ShowDialog(this);
            if (result == DialogResult.OK)
            {
                DBType = dialog.DBType;
                DbUser = dialog.DBUser;
                DbPass = dialog.DBPass;
                DbName = dialog.DBName;
                DbAddr = dialog.DBAddr;
                DbTNam = dialog.DBTNam;
            }
        } 
        #endregion

        #region 执行
        private void button3_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(IIS_DIR))
            {
                MessageBox.Show("请选择IIS 日志目录");
                return;
            }

            if (DBType == DBType.None)
            {
                MessageBox.Show("请设置目标数据库");
                return;
            }
            button1.Enabled = false;
            button2.Enabled = false;
            button3.Enabled = false;
            ThreadPool.QueueUserWorkItem(new WaitCallback(Run));
        } 
        #endregion

        #region 运行
        void Run(object state)
        {
            try
            {
                ReadLog();
            }
            catch(Exception ex)
            {
                OutputLog(string.Format("发生灾难性错误:{0}", ex.Message));
            }
        } 
        #endregion

        #region 开始读取日志
        private void ReadLog()
        {
            StringBuilder errors = new StringBuilder();
            DateTime all_st = DateTime.Now;

            int[] ReadFiledLength = new int[] { 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50 };
            int[] WriteFiledLength = new int[] { 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100 };

            //Dictionary<string, string> fileRelation = new Dictionary<string, string>();

            //foreach (var f in Directory.GetFiles(IIS_DIR, "*.log"))
            //{
            //    FileInfo info = new FileInfo(f);
            //    FileInfo info2 = new FileInfo(string.Format("{0}\\{1}.{2}", info.Directory.FullName, Guid.NewGuid().ToString().Replace("-", ""), "txt"));

            //    OutputLog(string.Format("开始处理文件{0}", info.Name));
            //    OutputLog(string.Format("复制到临时文件{0}..", info2.Name));
            //    info.CopyTo(info2.FullName);

            //    fileRelation.Add(info.FullName, info2.FullName);

            //    IDataReader reader = null;
            //    try
            //    {
            //        reader = ReadTxtFiledLengthByOleDb(info2.FullName);
            //    }
            //    catch
            //    {
            //        try
            //        {
            //            reader = ReadTxtByOdbc(info2.FullName);
            //        }
            //        catch
            //        {
            //            MessageBox.Show("不支持ODBC驱动,无法读取文本信息");
            //            return;
            //        }
            //    }

            //    if (reader.Read())
            //    {
            //        for (int i = 0; i < ReadFiledLength.Length; i++)
            //        {
            //            if (!reader.IsDBNull(i) && reader.GetInt32(i) > ReadFiledLength[i])
            //            {
            //                ReadFiledLength[i] = reader.GetInt32(i);
            //            }
            //        }
            //    }
            //    reader.Close();
            //}

            OutputLog(string.Format("创建临时表.."));
            string tmpName = CreateTempTable(WriteFiledLength);

            foreach (var f in Directory.GetFiles(IIS_DIR, "*.log"))
            {
                FileInfo info = new FileInfo(f);
                FileInfo info2 = new FileInfo(string.Format("{0}\\{1}.{2}", info.Directory.FullName, Guid.NewGuid().ToString().Replace("-", ""), "txt"));
                
                OutputLog(string.Format("开始处理文件{0}", info.Name));
                OutputLog(string.Format("复制到临时文件{0}..", info2.Name));
                info.CopyTo(info2.FullName);

                IDataReader reader = null;
                OutputLog(string.Format("开始读取临时文件{0}", info2.Name));
                try
                {
                    reader = ReadTxtByOleDb(info2.FullName, ReadFiledLength);
                }
                catch (Exception ex1)
                {
                    try
                    {
                        reader = ReadTxtByOdbc(info2.FullName);
                    }
                    catch (Exception ex2)
                    {
                        MessageBox.Show("不支持ODBC驱动,无法读取文本信息");
                        continue;
                    }
                }

                DateTime st = DateTime.Now;
                OutputLog(string.Format("开始写入数据库..."));

                #region 批量写入数据库
                try
                {
                    IDbConnection conn = new SqlConnection();
                    conn.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};Timeout=2147483647",
                        this.DbAddr,
                        this.DbName,
                        this.DbUser,
                        this.DbPass);
                    conn.Open();
                    SqlBulkCopy bulk = new SqlBulkCopy(conn as SqlConnection);
                    bulk.DestinationTableName = tmpName;
                    bulk.BulkCopyTimeout = int.MaxValue;
                    bulk.NotifyAfter = 100000;
                    bulk.BatchSize = 100000;
                    bulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulk_SqlRowsCopied);
                    bulk.WriteToServer(reader);
                    conn.Close();

                    DateTime nt = DateTime.Now;
                    OutputLog(string.Format("完成写入数据库,用时{0}秒", (nt - st).TotalSeconds));
                }
                catch (Exception sqlex)
                {
                    errors.AppendFormat("在导入文件{0}时发生错误:{1}", info.Name, sqlex.Message);
                }
                #endregion

                reader.Close();
                info2.Delete();
            }

            DeleteTempTableAndFinish(tmpName);

            this.Invoke(new EventHandler(delegate(object o, EventArgs e)
            {
                button1.Enabled = true;
                button2.Enabled = true;
                button3.Enabled = true;
            }));

            if (errors.Length > 0)
                OutputLog("发生错误的文件:\r\n" + errors.ToString());
            OutputLog(string.Format("已完成目录导入,总耗时{0}秒", (DateTime.Now - all_st).TotalSeconds));
        }

        void bulk_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            OutputLog(string.Format("已完成{0}行", e.RowsCopied));
        } 
        #endregion

        #region 创建临时表
        private string CreateTempTable(int[] FiledLength)
        {
            IDbConnection conn = new SqlConnection();
            conn.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};Timeout=2147483647",
                this.DbAddr,
                this.DbName,
                this.DbUser,
                this.DbPass);
            conn.Open();

            string sql = string.Empty;
            string tmpName = string.Empty;

            tmpName = "lzosft_com_temp";
            sql = @"if exists(select * from sysobjects where name ='" + tmpName + "') begin drop table [" + tmpName + "] end create table [" + tmpName + @"](
                        [CO1] varchar({0}),
                        [CO2] varchar({1}),
                        [CO3] varchar({2}),
                        [CO4] varchar({3}),
                        [CO5] varchar({4}),
                        [CO6] varchar({5}),
                        [CO7] varchar({6}),
                        [CO8] varchar({7}),
                        [CO9] varchar({8}),
                        [CO10] varchar({9}),
                        [CO11] varchar({10}),
                        [CO12] varchar({11}),
                        [CO13] varchar({12}),
                        [CO14] varchar({13}),
                        [CO15] varchar({14}),
                        [CO16] varchar({15}),
                        [CO17] varchar({16}),
                        [CO18] varchar({17}),
                        [CO19] varchar({18}),
                        [CO20] varchar({19}),
                        [CO21] varchar({20}),
                        [CO22] varchar({21}))";
            Object[] args = new Object[FiledLength.Length];
            Array.Copy(FiledLength, args, FiledLength.Length);

            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = string.Format(sql, args);
            cmd.ExecuteNonQuery();
            conn.Close();

            return tmpName;
        } 
        #endregion
                
        #region 输入到日志
        void OutputLog(string log)
        {
            this.textBox1.Invoke(new EventHandler(delegate(object o, EventArgs e)
            {
                this.textBox1.AppendText(log);
                this.textBox1.AppendText("\r\n");
            }));
        } 
        #endregion

        #region 读取Text文本并返回一个DataReader
        OdbcDataReader ReadTxtByOdbc(string fileFullName)
        {
            FileInfo info = new FileInfo(fileFullName);
            string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=";
            strConn += info.Directory.FullName;
            strConn += ";Extensions=asc,csv,tab,txt;";
            OdbcConnection objConn = new OdbcConnection(strConn);
            string strSQL = "select * from " + info.Name;
            OdbcCommand cmd = objConn.CreateCommand();
            cmd.CommandText = strSQL;
            objConn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        } 
        #endregion

        #region 读取Text文本并返回一个DataReader
        OleDbDataReader ReadTxtByOleDb(string fileFullName, int[] FiledLength)
        {
            FileInfo info = new FileInfo(fileFullName);
            string schema_ini = Path.Combine(info.Directory.FullName, "schema.ini");
            if (File.Exists(schema_ini))
                File.Delete(schema_ini);

            Object[] args = new Object[FiledLength.Length];
            Array.Copy(FiledLength, args, FiledLength.Length);

            //schema.ini文件内容如下
            //[t.txt] //文件名
            //ColNameHeader=NO //如果你的文件第一列不是字段名，加这行，否则不要
            //Format=Delimited( ) //（ ）中为分割符，注意空格，如果是其它的如~等都可以
            //Col1=n1 char width 10 //字段描述，如果第一行是字段名，这个可以省
            //Col2=n2 char width 10 //但也可以有。以修改字段名，类型，宽度等
            //col3 =n3 char width 10  
            File.WriteAllText(schema_ini, string.Format(@"[" + info.Name + @"]
Format=Delimited( )
Col1=n1 char width {0}
Col2=n2 char width {1}
Col3=n3 char width {2}
Col4=n4 char width {3}
Col5=n5 char width {4}
Col6=n6 char width {5}
Col7=n7 char width {6}
Col8=n8 char width {7}
Col9=n9 char width {8}
Col10=n10 char width {9}
Col11=n11 char width {10}
Col12=n12 char width {11}
Col13=n13 char width {12}
Col14=n14 char width {13}
Col15=n15 char width {14}
Col16=n16 char width {15}
Col17=n17 char width {16}
Col18=n18 char width {17}
Col19=n19 char width {18}
Col20=n20 char width {19}
Col21=n21 char width {20}
Col22=n22 char width {21}
", args));

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + info.Directory.FullName + ";Extended Properties='text;HDR=no;FMT=Delimited';";
            OleDbConnection objConn = new OleDbConnection(strConn);
            string strSQL = @"
select 
Left(n1,{0}) as n1,
Left(n2,{1}) as n2,
Left(n3,{2}) as n3,
Left(n4,{3}) as n4,
Left(n5,{4}) as n5,
Left(n6,{5}) as n6,
Left(n7,{6}) as n7,
Left(n8,{7}) as n8,
Left(n9,{8}) as n9,
Left(n10,{9}) as n10,
Left(n11,{10}) as n11,
Left(n12,{11}) as n12,
Left(n13,{12}) as n13,
Left(n14,{13}) as n14,
Left(n15,{14}) as n15,
Left(n16,{15}) as n16,
Left(n17,{16}) as n17,
Left(n18,{17}) as n18,
Left(n19,{18}) as n19,
Left(n20,{19}) as n20,
Left(n21,{20}) as n21,
Left(n22,{21}) as n22 from " + info.Name;
            //string strSQL = @"select * from " + info.Name;
            OleDbCommand cmd = objConn.CreateCommand();
            cmd.CommandText = string.Format(strSQL, args);
            objConn.Open();
            try
            {
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex) {
                throw ex;
            }
            finally
            {
                File.Delete(schema_ini);
            }
        }
        #endregion

        #region 读取Text文本各字段长度并返回一个DataReader
        OleDbDataReader ReadTxtFiledLengthByOleDb(string fileFullName)
        {
            FileInfo info = new FileInfo(fileFullName);
            string schema_ini = Path.Combine(info.Directory.FullName, "schema.ini");
            if (File.Exists(schema_ini))
                File.Delete(schema_ini);

            //schema.ini文件内容如下
            //[t.txt] //文件名
            //ColNameHeader=NO //如果你的文件第一列不是字段名，加这行，否则不要
            //Format=Delimited( ) //（ ）中为分割符，注意空格，如果是其它的如~等都可以
            //Col1=n1 char width 10 //字段描述，如果第一行是字段名，这个可以省
            //Col2=n2 char width 10 //但也可以有。以修改字段名，类型，宽度等
            //col3 =n3 char width 10  
            File.WriteAllText(schema_ini, @"[" + info.Name + @"]
Format=Delimited( )
Col1=n1 char width 5000
Col2=n2 char width 5000
Col3=n3 char width 5000
Col4=n4 char width 5000
Col5=n5 char width 5000
Col6=n6 char width 5000
Col7=n7 char width 5000
Col8=n8 char width 5000
Col9=n9 char width 5000
Col10=n10 char width 5000
Col11=n11 char width 5000
Col12=n12 char width 5000
Col13=n13 char width 5000
Col14=n14 char width 5000
Col15=n15 char width 5000
Col16=n16 char width 5000
Col17=n17 char width 5000
Col18=n18 char width 5000
Col19=n19 char width 5000
Col20=n20 char width 5000
Col21=n21 char width 5000
Col22=n22 char width 5000
");

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + info.Directory.FullName + ";Extended Properties='text;HDR=no;FMT=Delimited';";
            OleDbConnection objConn = new OleDbConnection(strConn);
            string strSQL = @"
select 
Max(Len(n1)) as n1,
Max(Len(n2)) as n2,
Max(Len(n3)) as n3,
Max(Len(n4)) as n4,
Max(Len(n5)) as n5,
Max(Len(n6)) as n6,
Max(Len(n7)) as n7,
Max(Len(n8)) as n8,
Max(Len(n9)) as n9,
Max(Len(n10)) as n10,
Max(Len(n11)) as n11,
Max(Len(n12)) as n12,
Max(Len(n13)) as n13,
Max(Len(n14)) as n14,
Max(Len(n15)) as n15,
Max(Len(n16)) as n16,
Max(Len(n17)) as n17,
Max(Len(n18)) as n18,
Max(Len(n19)) as n19,
Max(Len(n20)) as n20,
Max(Len(n21)) as n21,
Max(Len(n22)) as n22 from " + info.Name;
            OleDbCommand cmd = objConn.CreateCommand();
            cmd.CommandText = strSQL;
            objConn.Open();
            try
            {
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                File.Delete(schema_ini);
            }
        }
        #endregion

        #region 删除临时表并插入正式表中
        void DeleteTempTableAndFinish(string tmpName)
        {
            string sql = @"if exists(select * from sysobjects where name ='" + this.DbTNam + "') begin drop table [" + this.DbTNam + "] end select * INTO [" + this.DbTNam + "] FROM " + tmpName + " DELETE FROM [" + this.DbTNam + "] WHERE LEFT([CO1],1) = '#' drop table [" + tmpName + "]";
            
            IDbConnection conn = new SqlConnection();
            conn.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};Timeout=2147483647",
                this.DbAddr,
                this.DbName,
                this.DbUser,
                this.DbPass);
            conn.Open();

            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandTimeout = int.MaxValue;
            cmd.ExecuteNonQuery();

            conn.Close();
        }
        #endregion

        #region 转到联系我们
        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("iexplore.exe", "mailto://xuzhao1211@163.com");
        }

        private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("iexplore.exe", "tencent://message/?uin=634442729&Site");
        } 
        #endregion
    }
}
